﻿Imports System.Collections.Generic
Imports System.IO
Imports NPOI.HSSF.UserModel
Imports System.Data
Partial Class WebForm_ChangeIDStatus
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnUpload)
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If Me.FileUpload1.HasFile Then

            Try
                Dim workbook As New HSSFWorkbook(Me.FileUpload1.FileContent)
                Dim sheet As HSSFSheet = DirectCast(workbook.GetSheetAt(0), HSSFSheet)

                Dim table As New DataTable()
                table.TableName = "table"

                Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(0), HSSFRow)
                Dim cellCount As Integer = headerRow.LastCellNum
                table.Columns.Add("ID", Type.GetType("System.String"))
                Dim rowCount As Integer = sheet.LastRowNum

                For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum
                    Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
                    Dim dataRow As DataRow = table.NewRow()

                    row.GetCell(0).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    dataRow("ID") = row.GetCell(0).StringCellValue

                    table.Rows.Add(dataRow)
                Next

                workbook = Nothing
                sheet = Nothing
                Try
                    Dim dat As New clsData
                    Dim sql As New MyStringBuilder

                    Using scope As New Transactions.TransactionScope

                        For Each DR As DataRow In table.Rows
                            sql.Length = 0
                            sql.AppendFormat("update Volunteer set")
                            sql.AppendFormat(" Active='Y' ")
                            sql.AppendFormat(",UpdateDate={0} ", "getdate()")
                            sql.AppendFormat(",UpdateBy='{0}' ", Page.User.Identity.Name)
                            sql.AppendFormat(" where VolunteerID='{0}'; ", DR("ID").ToString())
                            dat.AccData(sql)
                        Next
                        scope.Complete()
                        MyObj.ShowMessage("上傳完成。")
                    End Using
                Catch ex As Exception
                    MyObj.ShowMessage("上傳檔資料無法存檔!!!")
                    Exit Sub
                End Try

            Catch ex As Exception
                MyObj.ShowMessage("無法正常讀取上傳檔資料!!!")
                Exit Sub

            End Try
        End If
    End Sub
End Class
